In a traditional database system there are many ways in which a database query can be executed. Where the query comprises several query predicates and references several database tables, there are several options as to the order in which these query predicates can be evaluated.
An optimizer determines the most cost effective ordering of execution of these query predicates within a database query. In a traditional optimizer, cardinality estimation for single table predicates and joins only uses demographic information. This demographic information, typically in the form of statistics, is defined on a single database table. In some cases demographic information is not available on a particular table. This lack of demographic information can affect the capability of an optimizer in estimating single-table cardinality and join cardinality. This in turn can lead to sub-optimal query plans.
A further difficulty is that some demographic information is captured in the form of a histogram. A histogram does not provide sufficient information for an optimizer to estimate the selectivity of complex predicates accurately. This inability to estimate selectivity accurately once again can lead to sub-optimal plans.
In some database systems some of the tables have associated single-table join indexes defined. It is typically less expensive to collect statistics on a join index than on a base table. It would be desirable to use information stored in a single-table join index to improve the accuracy of the cardinality estimation on its base table.